﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;

namespace MyCoreLib.Automation.Data.SqlClient.Metadata
{
    public class SqlSchemaReader2012 : SqlSchemaReader
    {        
        public SqlSchemaReader2012(IOutput output, string connectionString)
            : base(output, connectionString)
        {
            
        }

        public async override Task<string[]> LoadStoredProcedureFullNamesAsync(CancellationToken token)
        {
            using (var connection = new SqlConnection(ConnectionString))
            using (var command = new SqlCommand(SELECT_STORED_PROCEDURES, connection))
            {
                await connection.OpenAsync(token);
                var reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection, token);
                var list = new List<string>();
                while (reader.Read())
                {
                    list.Add(new GenericDbObject(reader.GetString(0), reader.GetString(1)).FullName);
                }
                return list.ToArray();
            }            
        }

        public override async Task<string[]> LoadUserTableTypeFullNamesAsync(CancellationToken token)
        {
            using (var connection = new SqlConnection(ConnectionString))
            using (var command = new SqlCommand(SELECT_USER_TABLE_TYPES, connection))
            {
                await connection.OpenAsync(token);
                var reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection, token);
                var list = new List<string>();
                while (reader.Read())
                {
                    list.Add(new GenericDbObject(reader.GetString(0), reader.GetString(1)).FullName);
                }
                return list.ToArray();
            }
        }

        public override async Task<SqlClrTypeMetadataCollection> LoadSqlClrTypeMetadataAsyn(CancellationToken token)
        {
            using (var connection = new SqlConnection(ConnectionString))
            using (var command = new SqlCommand(SELECT_SQLCLT_TYPES, connection))
            {
                await connection.OpenAsync(token);
                var reader = await command.ExecuteReaderAsync(CommandBehavior.CloseConnection, token);
                var list = new List<SqlClrTypeMetadata>();
                while (reader.Read())
                {
                    var schema = reader.GetString(0);
                    var name = reader.GetString(1);
                    var genericDbType = new GenericDbObject(schema, name);
                    list.Add(new SqlClrTypeMetadata(genericDbType.FullName));                    
                }
                return new SqlClrTypeMetadataCollection(list.ToArray());
            }
        }

        public override async Task<SqlUserTableTypeMetadataCollection> LoadSqlUserTableTypeMetadataAsyn(CancellationToken token)
        {
            var userTableTypeFullName = await this.LoadUserTableTypeFullNamesAsync(token);
            var collection = new SqlUserTableTypeMetadataCollection();
            var tasksList = new List<Task<SqlUserTableTypeMetadata>>();
            foreach (var fullName in userTableTypeFullName)
            {
                tasksList.Add(this.LoadItAsync(fullName, token));
            }

            await Task.WhenAll(tasksList);
            foreach (var task in tasksList)
            {
                collection.Add(task.Result);
            }
            return collection;
        }

        private async Task<SqlUserTableTypeMetadata> LoadItAsync(string userTableTypeFullName, CancellationToken token)
        {
            using (var connection = new SqlConnection(ConnectionString))
            using (var command = new SqlCommand(string.Format("declare @var as {0}; select * from @var", userTableTypeFullName), connection))
            {
                await connection.OpenAsync(token);
                var schema = new DataTable();
                new SqlDataAdapter(command).FillSchema(schema, SchemaType.Source);
                return new SqlUserTableTypeMetadata(userTableTypeFullName, schema);
            }
        }

        private const string SELECT_STORED_PROCEDURES = @"
            select s.name, p.name from sys.procedures as p
            inner join sys.schemas as s on s.schema_id = p.schema_id
            where type in ('P', 'PC')
            and s.name not in ('sys', 'INFORMATION_SCHEMA')
            and s.principal_id < 1000
        ";

        private const string SELECT_USER_TABLE_TYPES = @"
            select s.name as schema_name, tt.name as table_type_name
            from sys.table_types as tt
            inner join sys.schemas as s on s.schema_id = tt.schema_id
        ";

        private const string SELECT_SQLCLT_TYPES = @"
            select s.name as [schema], t.name as [name] from sys.assembly_types as t
            inner join sys.schemas as s on s.schema_id = t.schema_id
        ";        
    }
}
